<?php

namespace app\admin\controller;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use app\admin\model\Excels as ExcelsModel;
use think\Db;

class Excels extends Common
{
    //列表页
    public function lst() {
        //更新字段，计算小时数和花费
        $data1 = ExcelsModel::all();
        foreach ($data1 as $v){

            $start = $v['start_time']; //开始时间
            $startH = intval(substr($start,11,2)); //开始时间的时
            $startH = intval(substr($start,14,2)) > 50? $startH+1:$startH ; //开始时间的分大于50，时就加1

            $end = $v['end_time'];
            $endH = intval(substr($end,11,2)); //开始时间的时
            $endH = intval(substr($end,14,2)) > 10? $endH+1:$endH ; //开始时间的分大于50，时就加1


//            $v['startD'] = intval(substr($start,14,2));
//            $v['endD'] = intval(substr($end,14,2));

            //小时数
            if (strlen($start) != 16 || strlen($end) != 16) {
                $v['time_count'] = null;
            }else {
                $v['time_count'] = $endH-$startH;
            }

            //金额
            $v['cost'] = $v['time_count'] >= 1? 3+($v['time_count']-1)*1.5 : 3;

            $data = [
                'time_count'    =>  $v['time_count'],
                'cost'          =>  $v['cost']
            ];
            //更新数据
            ExcelsModel::get($v['id'])->isUpdate(true)->save($data);

        }
        return $this->fetch('lst');
    }

    //上传文件页
    public function ExcelUp() {
        return $this->fetch('excelUp');
    }

    public function update()
    {
        //获取表格的大小，限制上传表格的大小5M
        if(isset($_FILES['inputExcelclass'])){
            $file_size = $_FILES['inputExcelclass']['size'];
            if ($file_size > 5 * 1024 * 1024) {
                $this->error('文件大小不能超过5M');
                exit();
            }
        }
        //限制上传表格类型
        if(isset($_FILES['inputExcelclass'])){
            $fileExtendName = substr(strrchr($_FILES['inputExcelclass']["name"], '.'), 1);
        }
        //application/vnd.ms-excel  为xls文件类型
        if ($fileExtendName != 'xls' && $fileExtendName != 'xlsx') {
            $this->error('必须为excel表格，且必须为xls格式！');
            exit();
        }
        // 有Xls和Xlsx格式两种
        if( $fileExtendName =='xlsx' )
        {
            $objReader = IOFactory::createReader('Xlsx');
        }else{
            $objReader = IOFactory::createReader('Xls');
        }
        $objReader->setReadDataOnly(TRUE);
        $filename = $_FILES['inputExcelclass']['tmp_name'];
        $objPHPExcel = $objReader->load($filename);  //$filename可以是上传的表格，或者是指定的表格
        $sheet = $objPHPExcel->getSheet(0);   //excel中的第二张sheet（0表示第一张）
        $highestRow = $sheet->getHighestRow();       // 取得总行数
        $highestColumn = $sheet->getHighestColumn();   // 取得总列数
        \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);
        $lines = $highestRow -1;
        if($lines <= 0){
            $this->error('excel表格，没有数据！');
        }
        //循环读取excel表格，整合成数组。如果是不指定key的二维，就用$data[i][j]表示。
        for ($j = 3; $j <= $highestRow-1; $j++) {
            $data[$j - 3] = [
                'username' => trim($objPHPExcel->getActiveSheet()->getCell("D" . $j)->getValue()),
                'start_time' => trim($objPHPExcel->getActiveSheet()->getCell("E" . $j)->getValue()),
                'end_time' => trim($objPHPExcel->getActiveSheet()->getCell("F" . $j)->getValue()),
            ];
        }
        //使用模型
        $excels = new ExcelsModel();
        $res = $excels->saveAll($data);
        if ($res) {
            $this->success('上传成功！','admin/excels/lst');
        }

    }

    //获取excels表数据的接口
    public function getExcelDatas() {

        //获取请求参数
        $page = $this->request->param('page');
        $limit = $this->request->param('limit');

        $condition = $this->request->param('condition'); //查询的字段
        $content = $this->request->param('content'); //查询的内容

        $start = ($page-1)*$limit;

        if ($condition) {
            //搜索
            $data = ExcelsModel::whereLike($condition,'%'.$content.'%')->limit($start,$limit)->select();
            $count = ExcelsModel::whereLike($condition,'%'.$content.'%')->count();
        }else {
            $data = ExcelsModel::limit($start,$limit)->select();
            $count = count(ExcelsModel::all());
        }

        $datas = [
            'code' => 0,
            'msg'  => '查询成功',
            'data' => $data,
            'count'=> $count
        ];
        echo json_encode($datas);
        die;
    }

    //删除
    public function del() {
//        $excels = new ExcelsModel();
//        $excels->delete(true);
         $res = Db::name('excels')->delete(true);
         if ($res) {
             $this->success("删除成功~","admin/excels/lst");
         }else {
             $this->error('删除失败！');
         }
    }
}
